﻿Imports System.Data.SqlClient
Public Class frmREmpleadosExcel
    Dim mdsDatos As New DataSet
    Dim vigencia As Date
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)


    End Sub

    Private Sub frmREmpleadosExcel_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ToolStrip1.Cursor = Cursors.Hand
        ToolStrip1.RenderMode = System.Windows.Forms.ToolStripRenderMode.System
        Me.radioTodos.Checked = True

    End Sub
    Sub todos()
        Dim xl As Object
        Dim wb As Object
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 6

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim strSql As String = ""

        strSql = "select IDEmpleado,D.Descripcion as depto,P.Descripcion as puesto,n.Nombre as Nombrecillo,T.Descripcion as turno,Codigo,e.Nombre,ApellidoPaterno,FechaReingreso,ApellidoMaterno,Nombrecompleto,FechaNacimiento,LugarNacimiento,EstadoCivil,Sexo,CURP,CURPF,NSS,RFC,Homoclave,CuentaPago,EstadoEmpleado,SD,SDI,FechaAlta,CuentaCW,Telefono,CodigoPostal,Direccion,Poblacion,Estado,FechaBaja,CausaBaja,celular,telefonorecados,escolaridad"
        strSql = strSql & " from Empleados E INNER JOIN Departamentos D"
        strSql = strSql & " ON E.IdDepartamento=D.IdDepartamento"
        strSql = strSql & " INNER JOIN Puestos P ON E.IdPuesto=P.IdPuesto"
        strSql = strSql & " INNER JOIN Periodos N ON E.IdPeriodo=N.IdPeriodo"
        strSql = strSql & " INNER JOIN Turnos T ON E.IdTurno=T.IdTurno"
        strSql = strSql & " WHERE p.Descripcion='OPERADOR'"
        strSql = strSql & " and EstadoEmpleado='A'"
        strSql = strSql & " order by IdEmpleado"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        wb.SHEETS(1).cells(rng, 1).value = "Codigo"
        wb.SHEETS(1).cells(rng, 2).value = "Nombre"
        wb.SHEETS(1).cells(rng, 3).value = "Apellido Paterno"
        wb.SHEETS(1).cells(rng, 4).value = "Apellido Materno"
        wb.SHEETS(1).cells(rng, 5).value = "Nombre Completo"
        wb.SHEETS(1).cells(rng, 6).value = "Fecha Nacimiento"
        wb.SHEETS(1).cells(rng, 7).value = "Lugar Nacimiento"
        wb.SHEETS(1).cells(rng, 8).value = "Estado Civil"
        wb.SHEETS(1).cells(rng, 9).value = "Sexo"
        wb.SHEETS(1).cells(rng, 10).value = "CURP"
        wb.SHEETS(1).cells(rng, 11).value = "No. IMSS"
        wb.SHEETS(1).cells(rng, 12).value = "RFC"
        wb.SHEETS(1).cells(rng, 13).value = "Cuenta Banco"
        wb.SHEETS(1).cells(rng, 14).value = "SD"
        wb.SHEETS(1).cells(rng, 15).value = "SDI"
        wb.SHEETS(1).cells(rng, 16).value = "Departamento"
        wb.SHEETS(1).cells(rng, 17).value = "Puesto"
        wb.SHEETS(1).cells(rng, 18).value = "Periodo"
        wb.SHEETS(1).cells(rng, 19).value = "Turno"
        wb.SHEETS(1).cells(rng, 20).value = "Cuenta Compaq"
        wb.SHEETS(1).cells(rng, 21).value = "Direccion"
        wb.SHEETS(1).cells(rng, 22).value = "Codigo Postal"
        wb.SHEETS(1).cells(rng, 23).value = "Ciudad"
        wb.SHEETS(1).cells(rng, 24).value = "Estado"
        wb.SHEETS(1).cells(rng, 25).value = "Telefono"
        wb.SHEETS(1).cells(rng, 26).value = "Tel Celular"
        wb.SHEETS(1).cells(rng, 27).value = "Tel Recados"
        wb.SHEETS(1).cells(rng, 28).value = "Escolaridad"
        wb.SHEETS(1).cells(rng, 29).value = "Estado Empleado"
        wb.SHEETS(1).cells(rng, 30).value = "Fecha Alta"
        wb.SHEETS(1).cells(rng, 31).value = "Fecha Reingreso"
        wb.SHEETS(1).cells(rng, 32).value = "Fecha Baja"
        wb.SHEETS(1).cells(rng, 33).value = "Causa de la Baja"
        wb.SHEETS(1).cells(rng, 34).value = "Tarjeton"
        wb.SHEETS(1).cells(rng, 35).value = "Licencia"
        wb.SHEETS(1).cells(rng, 36).value = "Vigencia"

        Dim i As Integer
        For i = 1 To 36
            wb.SHEETS(1).cells(rng, i).font.bold = True
            wb.SHEETS(1).cells(rng, i).font.size = 9
        Next

        Dim fNac As Date
        Dim dia As String = ""
        Dim mes As String = ""
        Dim strFnac As String = ""
        Dim sexo As String = ""
        Dim estado As String = ""
        Dim ALTA As Date
        Dim re As Date
        Dim baja As Date
        Dim Civil As String = ""
        Dim anio As String = ""


        rng = rng + 1
        Dim cont As Integer = 0
        Do While rdBuscar.Read()
            cont = cont + 1
            dia = ""
            mes = ""
            strFnac = ""
            sexo = ""
            estado = ""
            Civil = ""
            anio = ""
            wb.SHEETS(1).cells(rng, 1).value = rdBuscar("Codigo").ToString.Trim
            wb.SHEETS(1).cells(rng, 2).value = rdBuscar("Nombre").ToString.Trim
            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("ApellidoPaterno").ToString.Trim
            wb.SHEETS(1).cells(rng, 4).value = rdBuscar("ApellidoMaterno").ToString.Trim
            wb.SHEETS(1).cells(rng, 5).value = rdBuscar("NombrecCompleto").ToString.Trim
            fNac = rdBuscar("FechaNacimiento")
            dia = fNac.Day
            mes = fNac.Month
            If dia < 10 Then
                dia = "0" & dia
            End If
            If mes < 10 Then
                mes = "0" & mes
            End If
            anio = Mid(fNac.Year, 3, 2)
            strFnac = anio & mes & dia
            wb.SHEETS(1).cells(rng, 6).value = fNac.Day & "/" & fNac.Month & "/" & fNac.Year
            wb.SHEETS(1).cells(rng, 7).value = rdBuscar("LugarNacimiento").ToString.Trim
            If rdBuscar("EstadoCivil").ToString.Trim = "C" Then
                Civil = "Casado"
            ElseIf rdBuscar("EstadoCivil").ToString.Trim = "S" Then
                Civil = "Soltero"
            ElseIf rdBuscar("EstadoCivil").ToString.Trim = "D" Then
                Civil = "Divorciado"
            ElseIf rdBuscar("EstadoCivil").ToString.Trim = "V" Then
                Civil = "Viudo"

            End If

            wb.SHEETS(1).cells(rng, 8).value = Civil
            If rdBuscar("Sexo").ToString.Trim = "M" Then
                sexo = "Masculino"
            Else
                sexo = "Femenino"
            End If
            wb.SHEETS(1).cells(rng, 9).value = sexo
            wb.SHEETS(1).cells(rng, 10).value = rdBuscar("CURP").ToString.Trim & strFnac & rdBuscar("CURPF").ToString.Trim
            wb.SHEETS(1).cells(rng, 11).value = rdBuscar("Nss").ToString.Trim
            wb.SHEETS(1).cells(rng, 12).value = rdBuscar("RFC").ToString.Trim & strFnac & rdBuscar("Homoclave").ToString.Trim
            wb.SHEETS(1).cells(rng, 13).value = rdBuscar("CuentaPago")
            wb.SHEETS(1).cells(rng, 14).value = rdBuscar("Sd")
            wb.SHEETS(1).cells(rng, 15).value = rdBuscar("Sdi")
            wb.SHEETS(1).cells(rng, 16).value = rdBuscar("depto").ToString.Trim
            wb.SHEETS(1).cells(rng, 17).value = rdBuscar("puesto").ToString.Trim
            wb.SHEETS(1).cells(rng, 18).value = rdBuscar("Nombrecillo").ToString.Trim
            wb.SHEETS(1).cells(rng, 19).value = rdBuscar("Turno").ToString.Trim
            wb.SHEETS(1).cells(rng, 20).value = rdBuscar("CuentaCW").ToString.Trim
            wb.SHEETS(1).cells(rng, 21).value = rdBuscar("Direccion").ToString.Trim
            wb.SHEETS(1).cells(rng, 22).value = rdBuscar("CodigoPostal")
            wb.SHEETS(1).cells(rng, 23).value = rdBuscar("Poblacion").ToString.Trim
            wb.SHEETS(1).cells(rng, 24).value = rdBuscar("Estado").ToString.Trim
            wb.SHEETS(1).cells(rng, 25).value = rdBuscar("Telefono").ToString.Trim
            wb.SHEETS(1).cells(rng, 26).value = rdBuscar("celular").ToString.Trim
            wb.SHEETS(1).cells(rng, 27).value = rdBuscar("telefonorecados").ToString.Trim
            wb.SHEETS(1).cells(rng, 28).value = rdBuscar("Escolaridad").ToString.Trim
            If rdBuscar("EstadoEmpleado").ToString.Trim = "A" Then
                estado = "Alta"
            ElseIf rdBuscar("EstadoEmpleado").ToString.Trim = "B" Then
                estado = "Baja"
            Else
                estado = "Reingreso"
            End If
            wb.SHEETS(1).cells(rng, 29).value = estado
            ALTA = rdBuscar("FechaAlta")
            wb.SHEETS(1).cells(rng, 30).value = ALTA.Day & "/" & ALTA.Month & "/" & ALTA.Year
            Try
                re = rdBuscar("FechaReingreso")
                baja = rdBuscar("FechaBaja")
            Catch ex As Exception

            End Try
            If estado <> "Alta" Then
                wb.SHEETS(1).cells(rng, 31).value = re.Day & "/" & re.Month & "/" & re.Year
                wb.SHEETS(1).cells(rng, 32).value = baja.Day & "/" & baja.Month & "/" & baja.Year

            End If

            wb.SHEETS(1).cells(rng, 33).value = rdBuscar("CausaBaja").ToString.Trim

            If rdBuscar("puesto").ToString.Trim = "OPERADOR" Then
                wb.SHEETS(1).cells(rng, 34).value = BuscarStrDatoCitra("Capacitacion", "Tarjeton", "status='ALTA' and idempleado=" & rdBuscar("idempleado"))
                wb.SHEETS(1).cells(rng, 35).value = BuscarStrDatoCitra("Licencias", "Licencia", "idempleado=" & rdBuscar("idempleado"))
                vigencia = BuscarDateDatoCitra("Licencias", "vigencia", "idempleado=" & rdBuscar("idempleado"))
                wb.SHEETS(1).cells(rng, 36).value = vigencia.Day & "/" & vigencia.Month & "/" & vigencia.Year
            Else
                wb.SHEETS(1).cells(rng, 34).value = ""
                wb.SHEETS(1).cells(rng, 35).value = ""
                wb.SHEETS(1).cells(rng, 36).value = ""
            End If
            For i = 1 To 36
                wb.SHEETS(1).cells(rng, i).font.size = 9
            Next
            rng = rng + 1
        Loop

        wb.SHEETS(1).columns(14).numberformat = "#,##0.00"
        wb.SHEETS(1).columns(15).numberformat = "#,##0.00"

        wb.SHEETS(1).COLUMNS(1).ColumnWidth = 8
        wb.SHEETS(1).COLUMNS(2).ColumnWidth = 22
        wb.SHEETS(1).COLUMNS(3).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(4).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(5).ColumnWidth = 40
        wb.SHEETS(1).COLUMNS(6).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(7).ColumnWidth = 20
        wb.SHEETS(1).COLUMNS(8).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(9).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(10).ColumnWidth = 25
        wb.SHEETS(1).COLUMNS(11).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(12).ColumnWidth = 25
        wb.SHEETS(1).COLUMNS(13).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(14).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(15).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(16).ColumnWidth = 32
        wb.SHEETS(1).COLUMNS(17).ColumnWidth = 30
        wb.SHEETS(1).COLUMNS(18).ColumnWidth = 18
        wb.SHEETS(1).COLUMNS(19).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(20).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(21).ColumnWidth = 48
        wb.SHEETS(1).COLUMNS(22).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(23).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(24).ColumnWidth = 20
        wb.SHEETS(1).COLUMNS(25).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(26).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(27).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(28).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(29).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(30).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(31).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(32).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(33).ColumnWidth = 40
        wb.SHEETS(1).COLUMNS(34).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(35).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(36).ColumnWidth = 12


        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Informe de Personal detallado. Filtro: Todos"
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso por: " & usuarioactivo & " el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
        wb.SHEETS(1).cells(4, 1).value = "Registros del filtro: " & cont
        wb.SHEETS(1).cells(4, 1).FONT.SIZE = 8
    End Sub


    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)


    End Sub

    Private Sub RadButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Close()
    End Sub

    Private Sub btnAbrir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub ToolStripButton2_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton2.Click
        todos()

    End Sub

    Private Sub ToolStripButton1_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton1.Click
        Close()

    End Sub
End Class